package com.netease.easyexcel.util;


import com.netease.easyexcel.model.CellDetail;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


import java.io.IOException;
import java.io.InputStream;
import java.text.NumberFormat;
import java.util.*;


public class ExcelReader {


    private XSSFWorkbook wb;   // 工作簿
    private XSSFSheet sheet;   // 工作表
    private XSSFRow row0;      // 标题行
    private XSSFRow rowN;      // 数据第一行

    /**
     * 读取Excel表格表头的内容
     *
     * @param is
     * @return String 表头内容的数组
     */
    public Map<String, List<CellDetail>> readExcelTitle(InputStream is) {

        if(is == null){
            return Collections.emptyMap();
        }

        Map<String, List<CellDetail>> sheetDetailMap = new LinkedHashMap<>();
        try {
            wb = new XSSFWorkbook(is);
        } catch (IOException e) {
            e.printStackTrace();
        }

        for(int i = 0; i < wb.getNumberOfSheets(); i++) {
            List<CellDetail> cellDetailList = new ArrayList<>();
            sheet = wb.getSheetAt(i);
            row0 = sheet.getRow(0);
            rowN = sheet.getRow(1);
            // 标题总列数
            int colNum = row0.getPhysicalNumberOfCells();
            for (int j = 0; j < colNum; j++) {
                //titleList.add(getCellFormatValue(row0.getCell(j)));
                String title = String.valueOf(row0.getCell(j).getStringCellValue());
                CellType cellType = getCellType(rowN.getCell(j));
                CellDetail cellDetail = new CellDetail(title,cellType);
                cellDetailList.add(cellDetail);
            }
            sheetDetailMap.put(sheet.getSheetName(),cellDetailList);
        }
        return sheetDetailMap;
    }



    /*
    *
    *   NUMERIC            数值型        0

        STRING             字符串型      1

        FORMULA            公式型        2

        BLANK              空值          3

        BOOLEAN            布尔型        4

        ERROR              错误          5
    *
    * */
    /**
     * 依据XSSFCell类型设置数据
     *
     * @param cell
     * @return
     */
    private String getCellFormatValue(XSSFCell cell) {
        String cellValue = "";
        if (cell != null) {
            // 推断当前Cell的Type
            if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    cellValue = DateFormatUtils.format(cell.getDateCellValue(), "yyyy-MM-dd");
                } else {
                    NumberFormat nf = NumberFormat.getInstance();
                    cellValue = String.valueOf(nf.format(cell.getNumericCellValue())).replace(",", "");
                }
            } else if (cell.getCellTypeEnum() == CellType.STRING) {
                cellValue = String.valueOf(cell.getStringCellValue());
            } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
                cellValue = String.valueOf(cell.getBooleanCellValue());
            } else if (cell.getCellTypeEnum() == CellType.ERROR) {
                cellValue = "Error Type";
            } else {
                cellValue = "";
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }

    private CellType getCellType(XSSFCell cell) {
        CellType cellType = CellType._NONE;
        if(cell != null) {
            if(cell.getCellTypeEnum() == CellType.NUMERIC) {
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    return CellType.STRING;
                }
                cellType = CellType.NUMERIC;
            } else if(cell.getCellTypeEnum() == CellType.STRING) {
                cellType = CellType.STRING;
            } else if(cell.getCellTypeEnum() == CellType.BOOLEAN) {
                cellType = CellType.BOOLEAN;
            } else {
                cellType = CellType.ERROR;
            }
        } else {
            cellType = CellType.BLANK;
        }

        return cellType;
    }





    /**
     * 读取Excel数据内容
     *
     * @param is
     * @return Map 包括单元格数据内容的Map对象
     */
    public Map<Integer, String> readExcelContent(InputStream is) {
        Map<Integer, String> content = new HashMap<Integer, String>();
        String str = "";
        try {
            wb = new XSSFWorkbook(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sheet = wb.getSheetAt(0);
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        row0 = sheet.getRow(0);
        int colNum1 = row0.getPhysicalNumberOfCells(); // 获取有数据的列的个数
        int colNum = 15;
        // 正文内容应该从第二行開始,第一行为表头的标题
        for (int i = 1; i <= rowNum; i++) {
            rowN = sheet.getRow(i);
            int j = 0;

            while (j < colNum) {
                String cell = getCellFormatValue(rowN.getCell((short) j)).trim();
                if (cell == "") {
                    j++;
                    continue;
                }
                str += cell + " ";
                j++;
            }
            str.trim();
            // System.out.println(str);
            content.put(i, str);
            str = "";
        }
        return content;
    }

}
